IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientBilling]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientBilling]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetPatientBilling
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[PatientBilling] (
   ID   Varchar(30),
   [CRNumber]     Varchar(50),
   [RcptNumber]     Varchar(50),
   [ChargeType]     Varchar(50),
   [Quantity]     Float,
   [Rate]     Float,
   [Amount]     Float,
   [AmountPaid]     Float,
   [DiscountType]     Varchar(50),
   [RecomandedBy]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientBilling TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatientBilling') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatientBilling
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_GetPatientBilling
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetPatientBilling
@vchID VARCHAR(30)
AS
   SELECT
       ID,
       CRNumber,
       RcptNumber,
       ChargeType,
       Quantity,
       Rate,
       Amount,
       AmountPaid,
       DiscountType,
       RecomandedBy
   FROM PatientBilling
   WHERE ID=@vchID
GO

GRANT EXEC ON dbo.SP_GetPatientBilling TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatientBilling') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatientBilling
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetPatientBilling
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetPatientBilling
@vchID Varchar(30),
@vchCRNumber    Varchar(50),
@vchRcptNumber    Varchar(50),
@vchChargeType    Varchar(50),
@fltQuantity    Float,
@fltRate    Float,
@fltAmount    Float,
@fltAmountPaid    Float,
@vchDiscountType    Varchar(50),
@vchRecomandedBy    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     ID
   FROM PatientBilling
   WHERE   (CRNumber = @vchCRNumber AND RcptNumber = @vchRcptNumber)
   )

   BEGIN
   INSERT INTO PatientBilling(
       ID,
       CRNumber,
       RcptNumber,
       ChargeType,
       Quantity,
       Rate,
       Amount,
       AmountPaid,
       DiscountType,
       RecomandedBy
  )
   VALUES (
      @vchID,
       @vchCRNumber,
       @vchRcptNumber,
       @vchChargeType,
       @fltQuantity,
       @fltRate,
       @fltAmount,
       @fltAmountPaid,
       @vchDiscountType,
       @vchRecomandedBy
   )
   END
ELSE  IF EXISTS(SELECT     ID
   FROM PatientBilling
   WHERE   (CRNumber = @vchCRNumber AND RcptNumber = @vchRcptNumber)
   )

   BEGIN
   UPDATE PatientBilling
   SET
       CRNumber = @vchCRNumber,
       RcptNumber = @vchRcptNumber,
       ChargeType = @vchChargeType,
       Quantity = @fltQuantity,
       Rate = @fltRate,
       Amount = @fltAmount,
       AmountPaid = @fltAmountPaid,
       DiscountType = @vchDiscountType,
       RecomandedBy = @vchRecomandedBy
  WHERE (CRNumber = @vchCRNumber AND RcptNumber = @vchRcptNumber)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetPatientBilling for Loan# : ' + CAST (@vchID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetPatientBilling TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeletePatientBilling') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeletePatientBilling
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_DeletePatientBilling
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeletePatientBilling
@vchCRNumber VARCHAR(20),
@vchRcptNumber VARCHAR(20)
AS
   DELETE FROM PatientBilling  WHERE   (CRNumber = @vchCRNumber AND RcptNumber = @vchRcptNumber)

GRANT EXEC ON dbo.SP_DeletePatientBilling TO hms_usr
GO

